<?php

/**
 * 特殊表現記錄
 * @author cik
 *
 */
class Default_Model_Upgrade_StudSemeSpe extends Default_Model_Upgrade_Core
{
    protected $_table = "stud_seme_spe";

    protected $_targetTable = array('student_excellence');

    public function upgrade()
    {
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {
            $hasData = 0 ;
            // 先判斷是否有資料
            $query = "SELECT COUNT(*)  AS cc FROM student_excellence";
            $res = $this->_newdbPdo->query($query)->fetch();
            $hasData = $res['cc'];

            // drop FOREIGN KEY
            $query = "ALTER TABLE `student_excellence` DROP FOREIGN KEY `student_excellence_ibfk_1`";
            $this->_newdbPdo->exec($query) ;
            $query = "ALTER TABLE `student_excellence`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);


            $query = "SELECT a.* ,b.student_sn FROM stud_seme_spe a,stud_base b WHERE a.stud_id=b.stud_id";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach($res as $row) {
                $arr = array();
                $year=  (int)substr($row['seme_year_seme'],0,-1);
                $semester = (int)substr($row['seme_year_seme'],-1);
                $arr['year'] = $year;
                $arr['semester'] = $semester;
                $arr['student_sn'] = $row['student_sn'];
                if ($hasData) {
                    $find = $this->_newdbPdo->query("SELECT student_sn FROM student_excellence
				  WHERE  student_sn ='{$arr['student_sn']}'
				  AND year ='{$arr['year']}'
				  AND semester ='{$arr['semester']}'
				  ");
                    if ($find->rowCount() > 0)
                    continue;
                }
                $query = "ALTER TABLE `student_excellence`  ENGINE = INNODB";
                $this->_newdbPdo->exec($query);
                $query = "ALTER TABLE `student_excellence`
  ADD CONSTRAINT `student_excellence_ibfk_1` FOREIGN KEY (`student_sn`) REFERENCES `student` (`sn`) ON UPDATE CASCADE;";
                $this->_newdbPdo->exec($query);

                $arr['date'] = $row['sp_date'];
                $arr['description'] = $this->iconv($row['sp_memo']);
                $arr['teacher_sn'] = $row['teach_id'];
                $arr['update_time'] = $row['update_time'];
                $this->fromArray('student_excellence',$arr);
            }
            $log['memo'] = 'stud_seme_spe  升級';
            $log['upgrade_time']	= date("Y-m-d h:i:s");

            $this->finishUpgrade($log);
        }
    }

}